﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ORMAC.CodeGen.DBObjects;

namespace ORMAC.CodeGen
{
    public class SQLShema:IDBSchema
    {
        #region IDBSchema Members
        public List<T> RetrieveSchema<T>(string connectionString) where T : DBObjectInfo, new()
        {
            if(typeof(T)==typeof(TableInfo))
                return RetrieveTableSchema(connectionString).Select<TableInfo,T>(s=>s as T).ToList<T>();
            else if (typeof(T) == typeof(SProcInfo))
                return RetrieveSprocSchema(connectionString).Select<SProcInfo, T>(s => s as T).ToList<T>();
            return null;
        }
        public List<SProcInfo> RetrieveSprocSchema(string connectionString)
        {
            IReader ormac = new OReader();
            ormac.SetConnectionString(connectionString);
            List<SProcInfo> sprocs = ormac.LoadFromDatabase<SProcInfo>();
            return sprocs;
        }
        /// <summary>
        /// Completes stored procedure information by adding return value information to it
        /// </summary>
        /// <param name="connectionString">database connection string</param>
        /// <param name="sprocs">list of selected stored procedures</param>
        /// <param name="tables">list of existing tables</param>
        public TableInfo CompleteSprocSchema(string connectionString,SProcInfo sproc, List<DBObjectInfo> dbObjects)
        {
            List<TableInfo> tables = null;
            SPHelper helper = new SPHelper();
            List<DBObjectInfo> tempTables = dbObjects.FindAll(o => o is TableInfo);
            if (tempTables != null)
                tables = tempTables.Select<DBObjectInfo, TableInfo>(a => a as TableInfo).ToList<TableInfo>();
            return helper.GetReturnInfo(connectionString, sproc, tables);
        }
        public List<TableInfo> RetrieveTableSchema(string connectionString) 
        {
            IReader ormac = new OReader();
            ormac.SetConnectionString(connectionString);
            List<TableInfo> tables = ormac.LoadFromDatabase<TableInfo>();
            List<JoinInfo> joins = ormac.LoadFromDatabase<JoinInfo>();
            //remove space in names
            foreach (var t in tables)
            {
                t.CodeName = t.Name.Replace(" ", "");
            }            
            //complete primary key info
            foreach (var t in tables)
            {
                if (t.PrimaryKeys != null && t.Columns!=null)
                {
                    foreach (PrimaryKeyInfo pKey in t.PrimaryKeys)
                    {
                        var col=t.Columns.Find(c => c.Name == pKey.KeyName);
                        col.IsPrimaryKey = true;
                    }
                }
            }
            if(joins!=null){
                foreach (var j in joins){
                    j.LeftTable = j.LeftTable.Replace(" ", "");
                    j.RightTable = j.RightTable.Replace(" ", "");
                }
            }
            //complete join information
            foreach (var t in tables)
            {
                List<JoinInfo> tJoins = joins.FindAll(j => j.RightTable == t.Name || j.LeftTable == t.Name);
                var tableInfo = t as TableInfo;
                if(tableInfo!=null)
                    tableInfo.Joins = tJoins;
                
            }
            //complete link information
            foreach (var t in tables)
            {
                var tableInfo = t as TableInfo;
                if (tableInfo.Columns.Count == 2 && tableInfo.Joins.Count == 2)
                {
                    LinkTableInfo info = new LinkTableInfo();
                    info.ParentCol = tableInfo.Joins[0].LeftColumn;
                    info.LinkTableParentCol = tableInfo.Joins[0].RightColumn;
                    info.LinkTableChildCol = tableInfo.Joins[1].RightColumn;
                    info.ChildTable = tableInfo.Joins[1].LeftTable;
                    info.LinkTable = tableInfo.Joins[0].RightTable;
                    info.ChildCol = tableInfo.Joins[1].LeftColumn;

                    //find the first table and add join info
                    var parentTable = tables.Find(tt => tt.Name == tableInfo.Joins[0].LeftTable) as TableInfo;
                    if (parentTable.LinkJoins == null)
                        parentTable.LinkJoins = new List<LinkTableInfo>();
                    parentTable.LinkJoins.Add(info);
                    //Remove the link table child from prev joins
                    parentTable.Joins.RemoveAll(j => j.LeftTable == info.LinkTable || j.RightTable == info.LinkTable);

                    info = new LinkTableInfo();
                    info.ParentCol = tableInfo.Joins[1].LeftColumn;
                    info.LinkTableParentCol = tableInfo.Joins[1].RightColumn;
                    info.LinkTableChildCol = tableInfo.Joins[0].RightColumn;
                    info.ChildTable = tableInfo.Joins[0].LeftTable;
                    info.LinkTable = tableInfo.Joins[1].RightTable;
                    info.ChildCol = tableInfo.Joins[0].LeftColumn;

                    //find the first table and add join info
                    parentTable = tables.Find(tt => tt.Name == tableInfo.Joins[1].LeftTable) as TableInfo;
                    if (parentTable.LinkJoins == null)
                        parentTable.LinkJoins = new List<LinkTableInfo>();
                    parentTable.LinkJoins.Add(info);
                    //Remove the link table child from prev joins
                    parentTable.Joins.RemoveAll(j => j.LeftTable == info.LinkTable || j.RightTable == info.LinkTable);
                }
            }
            return tables;
        }

        #endregion

   
    }
}
